﻿

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;



namespace ztymodbus
{
    public class Conn 
    {
        //private SqlConnection cnn;
       // private SqlCommand comm;
        //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnString());
        
        public static string ConnString()
        {

            //string ServiceAddress = "conn.json";  //本地路径文件名
            //StreamReader sr = new StreamReader(ServiceAddress);
            //string retString = sr.ReadToEnd();  // 读取全文本
            //sr.Close();

            ////对读取的内容处理
            //JObject job = (JObject)(JsonConvert.DeserializeObject(retString));
            //var value = job["connstr"].ToString();
            //Thread.Sleep(200);
            return "Data Source=172.16.41.43;Initial Catalog=zty-oa;User=sa;Password=xuas510283+-*/;MultipleActiveResultSets=true";
        }


        /// <summary>
        /// 支持数组Parameter 
        /// </summary>
        /// <param name="_dr">Sql 语句</param>
        /// <param name="Par">数组 SqlParameter [] Par</param>
        /// <returns></returns>
        public IDataReader SelectSql(string sql, SqlParameter[] Par=null)
        {
            
                using (SqlConnection con = new SqlConnection(ConnString()))
                {
                    con.Open();
                    using (SqlCommand com = new SqlCommand(sql, con))
                    {
                        if (Par != null) { com.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray()); }
                        using (SqlDataAdapter sda = new SqlDataAdapter(com))
                        {
                        using (DataSet ds = new DataSet())
                        {
                            sda.Fill(ds);
                            if (sda.Fill(ds, "temptable") == 0)
                            {
                                ds.Tables.Add(new DataTable());
                            }
                            com.Parameters.Clear();

                            sda.Dispose();
                            com.Dispose();
                            con.Close();
                            con.Dispose();
                            return ds.Tables[0].CreateDataReader();
                        }
                        }

                    }

                }
            
        }

        public int UpdataData(string _sql, SqlParameter[] Par=null)
        {
            using (SqlConnection cnn = new SqlConnection(Conn.ConnString()))
            {
                cnn.Open();
                //SqlParameter[] sp = null;
                using (SqlCommand comm = new SqlCommand(_sql, cnn))
                {
                    if (Par != null) comm.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray());
                    int i = comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                    comm.Dispose();
                    return i;
                }
            }

        }


        public int InsertSql(string _sql, SqlParameter[] Par=null)
        {
            using (SqlConnection cnn = new SqlConnection(Conn.ConnString()))
            {
                cnn.Open();
                //SqlParameter[] sp = null;
                using (SqlCommand comm = new SqlCommand(_sql + ";SELECT @@Identity", cnn))
                {
                    if (Par != null) comm.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray());
                    //SqlDataReader dr = comm.ExecuteReader();
                    int i = Convert.ToInt32(comm.ExecuteScalar());
                    comm.Parameters.Clear();
                    comm.Dispose();
                    return i;
                }
            }
        }

        public string InsertSql_uuid(string _sql, SqlParameter[] Par = null)
        {

            using (SqlConnection cnn = new SqlConnection(Conn.ConnString()))
            {
                cnn.Open();
                //SqlParameter[] sp = null;
                using (SqlCommand comm = new SqlCommand(_sql + ";SELECT @@Identity", cnn))
                {
                    if (Par != null) comm.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray());
                    //SqlDataReader dr = comm.ExecuteReader();
                    string i = comm.ExecuteScalar().ToString();
                    comm.Parameters.Clear();
                    comm.Dispose();
                    return i;
                }
            }
        }

    }

    public class ConnOA2
    {
        //private SqlConnection cnn;
        // private SqlCommand comm;
        //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnString());

        public string ConnString()
        {

            //string ServiceAddress = "conn.json";  //本地路径文件名
            //StreamReader sr = new StreamReader(ServiceAddress);
            //string retString = sr.ReadToEnd();  // 读取全文本
            //sr.Close();

            ////对读取的内容处理
            //JObject job = (JObject)(JsonConvert.DeserializeObject(retString));
            //var value = job["connstr"].ToString();
            //Thread.Sleep(200);
            return "Data Source=172.16.41.43;Initial Catalog=zty-oa;User=sa;Password=xuas510283+-*/;MultipleActiveResultSets=true";
        }


        /// <summary>
        /// 支持数组Parameter 
        /// </summary>
        /// <param name="_dr">Sql 语句</param>
        /// <param name="Par">数组 SqlParameter [] Par</param>
        /// <returns></returns>
        public IDataReader Select_Sql(string sql, SqlParameter[] Par = null)
        {

            using (SqlConnection con = new SqlConnection(ConnString()))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    if (Par != null) { com.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray()); }
                    using (SqlDataAdapter sda = new SqlDataAdapter(com))
                    {
                        using (DataSet ds = new DataSet())
                        {
                            sda.Fill(ds);
                            if (sda.Fill(ds, "temptable") == 0)
                            {
                                ds.Tables.Add(new DataTable());
                            }
                            com.Parameters.Clear();

                            sda.Dispose();
                            com.Dispose();
                            con.Close();
                            con.Dispose();
                            return ds.Tables[0].CreateDataReader();
                        }
                    } 

                }

            }

        }

        public int UpdataData(string _sql, SqlParameter[] Par = null)
        {
            using (SqlConnection cnn = new SqlConnection(ConnString()))
            {
                cnn.Open();
                //SqlParameter[] sp = null;
                using (SqlCommand comm = new SqlCommand(_sql, cnn))
                {
                    if (Par != null) comm.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray());
                    int i = comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                    comm.Dispose();
                    cnn.Close();
                    cnn.Dispose();
                    return i;
                }
            }

        }


        public string InsertSql(string _sql, SqlParameter[] Par = null)
        {
            using (SqlConnection cnn = new SqlConnection(ConnString()))
            {
                cnn.Open();
                //SqlParameter[] sp = null;
                using (SqlCommand comm = new SqlCommand(_sql + ";SELECT @@Identity", cnn))
                {
                    if (Par != null) comm.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray());
                    //SqlDataReader dr = comm.ExecuteReader();
                    string i = Convert.ToString(comm.ExecuteScalar());
                    
                    comm.Parameters.Clear();
                    
                    comm.Dispose();
                    cnn.Close();
                    cnn.Dispose();
                    return i;
                }
            }
        }

    }


    public class PGConn
    {
        public string ConnString()
        {
            return "Host=172.16.41.88:2432;Username=postgres;Password=xuas510283+-*/;Database=zty_iot";
        }

        public IDataReader Select_Sql(string _sql, NpgsqlParameter[] Par = null)
        {

            using (NpgsqlConnection connection = new NpgsqlConnection(ConnString()))
            {
                connection.Open();
                using (NpgsqlCommand command = new NpgsqlCommand(_sql, connection))
                {
                    using (NpgsqlDataAdapter sda = new NpgsqlDataAdapter(command))
                    {
                        using (DataSet ds = new DataSet("ds"))
                        {
                            if (Par != null) command.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray());
                            sda.Fill(ds);
                            if (sda.Fill(ds, "temptable") == 0)
                            {
                                ds.Tables.Add(new DataTable());
                            }
                            command.Parameters.Clear();
                            return ds.Tables[0].CreateDataReader();
                        }
                    }

                }
            }


        }


        public string InsertSql(string _sql, NpgsqlParameter[] Par = null)
        {

            using (NpgsqlConnection cnn = new NpgsqlConnection(ConnString()))
            {
                cnn.Open();
                //SqlParameter[] sp = null;
                using (NpgsqlCommand comm = new NpgsqlCommand(_sql + " RETURNING uuid ", cnn))
                {
                    if (Par != null) comm.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray());
                    //SqlDataReader dr = comm.ExecuteReader();
                    //int i = comm.ExecuteNonQuery();
                    string i = Convert.ToString(comm.ExecuteScalar());
                    comm.Parameters.Clear();
                    comm.Dispose();
                    return i;
                }
            }
        }

        public int UpdataData(string _sql, NpgsqlParameter[] Par = null)
        {
            using (NpgsqlConnection cnn = new NpgsqlConnection(ConnString()))
            {
                cnn.Open();
                //SqlParameter[] sp = null;
                using (NpgsqlCommand comm = new NpgsqlCommand(_sql, cnn))
                {
                    if (Par != null) comm.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray());
                    int i = comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                    comm.Dispose();
                    return i;
                }
            }

            //comm = new SqlCommand(_sql, cnn);
            //comm.Parameters.AddRange(Par);

            //i = comm.ExecuteNonQuery();
            //comm.Parameters.Clear();
            //return i;
        }


    }


    public class ConnMySQL100
    {
        public static string ConnString()
        {
            string temp = "";
            try
            {
                string ServiceAddress = "appsettings.json";  //本地路径文件名
                StreamReader sr = new StreamReader(ServiceAddress);
                string retString = sr.ReadToEnd();  // 读取全文本
                sr.Close();

                //对读取的内容处理
                JObject job = (JObject)(JsonConvert.DeserializeObject(retString));
                if (job.ContainsKey("connStr71_100"))
                {
                    temp = job["connStr71_100"].ToString();
                }
            }
            catch { }
            //var builder = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json");
            //var config = builder.Build();
            //temp = config[keyname].ToString();
            //return temp;
            return "server=172.16.71.100; user id=zty; password=zty147258-=;database=monitor_lanya;sslmode=None;";
        }

        //jdbc-url: jdbc:mysql://172.16.31.10:3306/zty-iot?useUnicode=true&autoReconnect=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
        //username: zty   password: mXRb&ImYXxKE7p%8
        //string s = "server=localhost; user id=root; password=;database=cSharp;";

        public IDataReader Select_Sql(string _sql, MySqlParameter[] Par = null)
        {

            using (MySqlConnection connection = new MySqlConnection(ConnString()))
            {
                connection.Open();
                using (MySqlCommand command = new MySqlCommand(_sql, connection))
                {
                    using (MySqlDataAdapter sda = new MySqlDataAdapter(command))
                    {
                        using (DataSet ds = new DataSet("ds"))
                        {
                            if (Par != null) command.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray());
                            sda.Fill(ds);
                            if (sda.Fill(ds, "temptable") == 0)
                            {
                                ds.Tables.Add(new DataTable());
                            }
                            command.Parameters.Clear();
                            connection.Close();
                            connection.Dispose();
                            return ds.Tables[0].CreateDataReader();
                        }
                    }

                }
            }


        }

        public int InsertSql(string _sql, MySqlParameter[] Par = null)
        {

            using (MySqlConnection cnn = new MySqlConnection(ConnString()))
            {
                cnn.Open();
                //SqlParameter[] sp = null;
                using (MySqlCommand comm = new MySqlCommand(_sql + ";SELECT @@Identity", cnn))
                {
                    if (Par != null) comm.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray());
                    //SqlDataReader dr = comm.ExecuteReader();
                    //int i = comm.ExecuteNonQuery();
                    int i = Convert.ToInt32(comm.ExecuteScalar());
                    comm.Parameters.Clear();
                    comm.Dispose();
                    cnn.Close();
                    cnn.Dispose();
                    return i;
                }
            }
        }

        public int UpdataData(string _sql, MySqlParameter[] Par = null)
        {
            using (MySqlConnection cnn = new MySqlConnection(ConnString()))
            {
                cnn.Open();
                //SqlParameter[] sp = null;
                using (MySqlCommand comm = new MySqlCommand(_sql, cnn))
                {
                    if (Par != null) comm.Parameters.AddRange(Par.Select(x => ((ICloneable)x).Clone()).ToArray());
                    int i = comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                    comm.Dispose();
                    cnn.Close();
                    cnn.Dispose();
                    return i;
                }
            }

            //comm = new SqlCommand(_sql, cnn);
            //comm.Parameters.AddRange(Par);

            //i = comm.ExecuteNonQuery();
            //comm.Parameters.Clear();
            //return i;
        }




    }

}
